import sqlite3
import json
import os

# Remove existing database if it exists
if os.path.exists('emailqa.db'):
    os.remove('emailqa.db')

# Create test database
conn = sqlite3.connect('emailqa.db')
cursor = conn.cursor()

# Create table
cursor.execute('''
    CREATE TABLE emailqa (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        system_conversation_id TEXT,
        q_body TEXT,
        q_subject TEXT,
        q_real_question TEXT,
        q_question_subject TEXT,
        q_attachment_content TEXT,
        customer_service_answer TEXT,
        context_email_conversation_history TEXT,
        context_email_conversation_history_symmary TEXT,
        context_order_info TEXT
    )
''')

# Insert test data
test_data = [
    (1, 'conv1', 'body1', 'subject1', 'real1', 'q_subject1', 'attach1', 
     'Hi TEAM, answer', 'history1', 'summary1', '{"tracking_details": {"trackings": [1,2], "other": "data"}}'),
    (2, 'conv2', 'body2', 'subject2', 'real2', 'q_subject2', 'attach2', 
     'Regular answer', 'history2', 'summary2', '{"tracking_details": {"other": "data"}}'),
    (3, 'conv3', 'body3', 'subject3', 'real3', 'q_subject3', 'attach3', 
     'Hi TEAM, another answer', 'history3', 'summary3', '{}'),
    (4, 'conv4', 'body4', 'subject4', 'real4', 'q_subject4', 'attach4', 
     'Normal response', 'history4', 'summary4', '{"tracking_details": {"trackings": [3,4], "status": "active"}}')
]

cursor.executemany('''
    INSERT INTO emailqa VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
''', test_data)

conn.commit()
conn.close()

print('Test database created successfully')